/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package org.ipo.mm.ejb.util;

/**
 *
 * @author makcro
 */
public class InstitutionSearchQueryBuilder {

    private final String nameFilter;
    private final String nameOperator;
    private final String addressFilter;
    private final String addressOperator;
    private final String emailFilter;
    private final String emailOperator;
    private final String telephoneFilter;
    private final String telephoneOperator;
    private final String faxFilter;
    private final String faxOperator;
    private final String representativeFilter;
    private final String representativeOperator;
    private final String[] typeFilter;
    private final String[] regionFilter;
    private final String[] familyFilter;
    private boolean whereClauseAdded = false;
    private String query = new String();
    private final String[] cityFilter;
    private final String orderBy;
    private final boolean isCount;
    private final boolean isQuickSearch;
    private String nextConditionClause = null;
    private boolean searchClauseOpened = false;

    public InstitutionSearchQueryBuilder(
            String nameFilter,
            String nameOperator,
            String addressFilter,
            String addressOperator,
            String emailFilter,
            String emailOperator,
            String telephoneFilter,
            String telephoneOperator,
            String faxFilter,
            String faxOperator,
            String representativeFilter,
            String representativeOperator,
            String[] typeFilter,
            String[] regionFilter,
            String[] familyFilter,
            String[] cityFilter,
            String orderBy,
            boolean isCount,
            boolean isQuickSearch) {
        this.nameFilter = nameFilter;
        this.nameOperator = nameOperator;
        this.addressFilter = addressFilter;
        this.addressOperator = addressOperator;
        this.emailFilter = emailFilter;
        this.emailOperator = emailOperator;
        this.telephoneFilter = telephoneFilter;
        this.telephoneOperator = telephoneOperator;
        this.faxFilter = faxFilter;
        this.faxOperator = faxOperator;
        this.representativeFilter = representativeFilter;
        this.representativeOperator = representativeOperator;
        this.typeFilter = typeFilter;
        this.regionFilter = regionFilter;
        this.familyFilter = familyFilter;
        this.cityFilter = cityFilter;
        this.orderBy = orderBy;
        this.isCount = isCount;
        this.isQuickSearch = isQuickSearch;

        if (this.isCount) {
            query = new String("SELECT COUNT(DISTINCT inst) FROM Institution inst WHERE inst.deleted = false ");
        } else {
                query = new String("SELECT DISTINCT inst FROM Institution inst LEFT JOIN FETCH inst.representativeCollection  WHERE inst.deleted = false ");
        }


        if (this.isQuickSearch) {
            nextConditionClause = " OR ";
        } else {
            nextConditionClause = " AND";
        }


    }



    public String getQuery() {


        if (this.nameFilter != null) {

            addNextConditionClause();


            query += " ( ";
            String expression = buildSimpleCondition(nameFilter, nameOperator, "i", "name");
            query += expression;
            query += " ) ";

        }

        if (this.addressFilter != null) {

            addNextConditionClause();

            query += " ( ";
            String expression = buildSimpleCondition(addressFilter, addressOperator, "i", "address");
            query += expression;
            query += " ) ";

        }

        if (this.emailFilter != null) {

            addNextConditionClause();


            query += " ( ";
            String expression = buildSimpleCondition(emailFilter, emailOperator, "i", "email");
            query += expression;
            query += " ) ";

        }

        if (this.telephoneFilter != null) {
            addNextConditionClause();


            query += " ( ";
            String expression = buildSimpleCondition(telephoneFilter, telephoneOperator, "i", "telephone");
            query += expression;
            query += " ) ";

        }

        if (this.faxFilter != null) {

            addNextConditionClause();


            query += " ( ";
            String expression = buildSimpleCondition(faxFilter, faxOperator, "i", "fax");
            query += expression;
            query += " ) ";

        }

        if (this.representativeFilter != null) {

            addNextConditionClause();

            query += " ( ";
            String expression = buildNameCondition(representativeFilter, representativeOperator);
            query += expression;
            query += " ) ";

        }

        if (this.typeFilter != null) {
            addNextConditionClause();


            query += " ( ";
            String expression = " i.type.id " + buildSetCondition(typeFilter);
            query += expression;
            query += " ) ";
        }

        if (this.familyFilter != null) {
            addNextConditionClause();


            query += " ( ";
            String expression = " i.family.id " + buildSetCondition(familyFilter);
            query += expression;
            query += " ) ";
        }

        if (this.regionFilter != null) {
            addNextConditionClause();


            query += " ( ";
            String expression = " i.region.id " + buildSetCondition(regionFilter);
            query += expression;
            query += " ) ";
        }

        if (this.cityFilter != null) {

            addNextConditionClause();


            query += " ( ";
            String expression = " i.city.id " + buildSetCondition(cityFilter);
            query += expression;
            query += " ) ";
        }


        closeSearchClause();
        
        if (this.orderBy != null && !this.isCount) {
            String orderByClause = " \nORDER BY inst." + orderBy;
            query += orderByClause;
        }

        
        

        return query;

    }

    private void openSearchCluase() {
        String clause = " AND inst.id IN ( SELECT i.id FROM Institution i LEFT JOIN i.representativeCollection r WHERE i.deleted = false AND (\n";
        query += clause;
        this.searchClauseOpened = true;

    }

    private void closeSearchClause(){
        if(this.searchClauseOpened){
            String closeExp = " ))\n";
            query += closeExp;
        }
    }
    private void addNextConditionClause() {
        if(!this.searchClauseOpened){
            openSearchCluase();
        }
        else {
            query += nextConditionClause;
        }

    }

    

    private String buildSimpleCondition(String filter,
            String filterOper,
            String entity,
            String attribute) {

        String expresion = "";
        String[] filters = filter.split(" ");



        for (int i = 0; i < filters.length; i++) {
            String someFilter = filters[i];
            expresion += " " + entity + "." + attribute + " LIKE '%" + someFilter + "%' ";
            if (i != filters.length - 1) {
                expresion += " " + filterOper + " ";
            }

        }
        return expresion;

    }

    private String buildSetCondition(String[] filter) {
        String expresion = " IN (";
        for (int i = 0; i < filter.length; i++) {
            String value = filter[i];
            expresion += value;
            if (i != filter.length - 1) {
                expresion += ", ";
            }

        }
        expresion += ")";

        return expresion;
    }

    private String buildNameCondition(String filter,
            String filterOper) {

        String expresion = "";
        String[] filters = filter.split(" ");
        for (int i = 0; i < filters.length; i++) {
            String someFilter = filters[i];

            expresion += " ( r.firstName LIKE '%" + someFilter + "%' OR r.lastName LIKE '%" + someFilter + "%')";

            if (i != filters.length - 1) {
                expresion += " " + filterOper + " ";
            }
        }

        return expresion;


    }
}
